unit ExpensesForm;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, DBModule, Vcl.StdCtrls, Vcl.ComCtrls,
  Vcl.DBCtrls, Vcl.Grids, Vcl.DBGrids, UserUtils, IB;

type
  TfmExpenses = class(TForm)
    GroupBox1: TGroupBox;
    lblDate: TLabel;
    Label1: TLabel;
    Label3: TLabel;
    Label2: TLabel;
    txtYear: TEdit;
    txtAmount: TEdit;
    Label5: TLabel;
    Label4: TLabel;
    mmRemark: TMemo;
    Label6: TLabel;
    dbcbExpenseCategory: TDBLookupComboBox;
    UpDown1: TUpDown;
    UpDown2: TUpDown;
    txtMonth: TEdit;
    Label7: TLabel;
    Label8: TLabel;
    Label9: TLabel;
    Label10: TLabel;
    Label11: TLabel;
    Label12: TLabel;
    Label13: TLabel;
    txtReference: TEdit;
    dtDueDate: TDateTimePicker;
    dtPaymentDate: TDateTimePicker;
    chkBillable: TCheckBox;
    dbcbPaymentType: TDBLookupComboBox;
    txtPaidAmount: TEdit;
    txtPaymentReference: TEdit;
    DBGrid1: TDBGrid;
    Label14: TLabel;
    btnNew: TButton;
    btnEdit: TButton;
    btnSave: TButton;
    btnClose: TButton;
    btnDelete: TButton;
    cbName: TComboBox;
    procedure DBGrid1TitleClick(Column: TColumn);
    procedure DBGrid1CellClick(Column: TColumn);
    procedure btnCloseClick(Sender: TObject);
    procedure btnNewClick(Sender: TObject);
    procedure btnEditClick(Sender: TObject);
    procedure btnSaveClick(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormCreate(Sender: TObject);
    procedure btnDeleteClick(Sender: TObject);
    procedure txtPaidAmountKeyPress(Sender: TObject; var Key: Char);
    procedure txtAmountKeyPress(Sender: TObject; var Key: Char);
    procedure txtAmountExit(Sender: TObject);
    procedure txtPaidAmountExit(Sender: TObject);
    procedure cbNameDropDown(Sender: TObject);
  private
    { Private declarations }
    Operation: TOperation;

    procedure ClearDetails;
    procedure FillDetails;
    procedure InsertRecord;
    procedure UpdateRecord;
    procedure DeleteRecord;
  public
    { Public declarations }
  end;

var
  fmExpenses: TfmExpenses;

implementation

{$R *.dfm}

procedure TfmExpenses.DBGrid1CellClick(Column: TColumn);
begin
  btnEdit.Click;
end;

procedure TfmExpenses.DBGrid1TitleClick(Column: TColumn);
var
  sSQL: string;
begin
  with DataModule1.qryExpenses do
  begin
    Close;
    sSQL := SQLBeforeClause(SQL.Text, 'ORDER BY');
    SQL.Clear;
    SQL.Add(sSQL);
    SQL.Add(' ORDER BY "' + Column.FieldName + '"');
    Open;
  end;
end;

procedure TfmExpenses.btnCloseClick(Sender: TObject);
begin
  Close;
end;

procedure TfmExpenses.btnDeleteClick(Sender: TObject);
begin
  DeleteRecord;
end;

procedure TfmExpenses.btnEditClick(Sender: TObject);
begin
  Operation := opEdit;
  FillDetails;
end;

procedure TfmExpenses.btnNewClick(Sender: TObject);
begin
  Operation := opNew;
  ClearDetails;
end;

procedure TfmExpenses.btnSaveClick(Sender: TObject);
begin
  case Operation of
    opNew: InsertRecord;
    opEdit: UpdateRecord;
  end;
  btnNew.Click;
end;

procedure TfmExpenses.ClearDetails;
begin
  lblDate.Caption := DateToStr(Date);
  cbName.Text := '';
  dbcbExpenseCategory.KeyValue := -1;
  UpDown1.Position := StrToInt(FormatDateTime('yyyy', Date));
  UpDown2.Position := StrToInt(FormatDateTime('m', Date));
  txtAmount.Text := '0.00';
  txtReference.Text := '';
  chkBillable.Checked := False;
  dtDueDate.DateTime := Now;
  dtPaymentDate.DateTime := Now;
  dbcbPaymentType.KeyValue := '';
  txtPaidAmount.Text := '0.00';
  txtPaymentReference.Text := '';
  mmRemark.Lines.Clear;
end;

procedure TfmExpenses.cbNameDropDown(Sender: TObject);
begin
  with DataModule1.fbSupplier do
  begin
    if not Active then
      Open;
    cbName.Items.Clear;
    First;
    while not Eof do
    begin
      cbName.Items.Add(FieldByName('COMPANY_NAME').AsString);
      Next;
    end;
  end;
end;

procedure TfmExpenses.FillDetails;
var
  Billable: boolean;
begin
  lblDate.Caption := DateToStr(Date);
  with DBGrid1.DataSource.DataSet do
  begin
    cbName.Text := FieldByName('NAME').AsString;
    dbcbExpenseCategory.KeyValue := FieldByName('CATEGORY_ID').AsVariant;
    UpDown1.Position := FieldByName('PERIOD_YEAR').AsInteger;
    UpDown2.Position := FieldByName('PERIOD_MONTH').AsInteger;
    txtAmount.Text := Format('%.2f', [FieldByName('AMOUNT').AsFloat]);
    txtReference.Text := FieldByName('REFERENCE').AsString;
    if not TryStrToBool(FieldByName('BILLABLE').AsString, Billable) then
      Billable := False;
    chkBillable.Checked := Billable;
    dtDueDate.DateTime := FieldByName('DUE_DATE').AsDateTime;
    dtPaymentDate.DateTime := FieldByName('PAYMENT_DATE').AsDateTime;
    dbcbPaymentType.KeyValue := FieldByName('PAYMENT_TYPE').AsVariant;
    txtPaidAmount.Text := Format('%.2f', [FieldByName('PAID_AMOUNT').AsFloat]);
    txtPaymentReference.Text := FieldByName('PAYMENT_REFERENCE').AsString;
    mmRemark.Lines.Text := FieldByName('REMARK').AsString;
  end;
end;

procedure TfmExpenses.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  FreeAndNil(fmExpenses);
end;

procedure TfmExpenses.FormCreate(Sender: TObject);
begin
  with DataModule1 do
  begin
    with fbSupplier do
    begin
      if not Active then
        Open;
      Last;
      First;
    end;
    with fbExpenseCategory do
    begin
      if not Active then
        Open;
      Last;
      First;
    end;
    with fbPaymentType do
    begin
      if not Active then
        Open;
      Last;
      First;
    end;
    with qryExpenses do
    begin
      if not Active then
        Open;
      Last;
      First;
    end;
  end;
  ClearDetails;
end;

procedure TfmExpenses.InsertRecord;
begin
  with DataModule1.qrySQL do
  begin
    SQL.Clear;
    Params.Clear;
    try
      try
        SQL.Add('INSERT INTO EXPENSES (NAME, CATEGORY_ID, PERIOD_YEAR, ' +
                'PERIOD_MONTH, AMOUNT, REFERENCE, PAYMENT_DATE, PAYMENT_TYPE, ' +
                'PAYMENT_REFERENCE, DUE_DATE, PAID_AMOUNT, BILLABLE, REMARK, ' +
                'CREATE_DATE, UPDATE_DATE) VALUES (:Name, :CategoryID, ' +
                ':Year, :Month, :Amount, :Reference, :PaymentDate, :PaymentType, ' +
                ':PaymentRef, :DueDate, :PaidAmount, :Billable, :Remark, ' +
                'CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)');
        Params.ParamByName('Name').AsString := cbName.Text;
        Params.ParamByName('CategoryID').AsInteger := dbcbExpenseCategory.KeyValue;
        Params.ParamByName('Year').AsInteger := StrToInt(txtYear.Text);
        Params.ParamByName('Month').AsInteger := StrToInt(txtMonth.Text);
        Params.ParamByName('Amount').AsFloat := StrToFloat(txtAmount.Text);
        Params.ParamByName('Reference').AsString := txtReference.Text;
        Params.ParamByName('DueDate').AsDate := dtDueDate.Date;
        Params.ParamByName('PaymentDate').AsDate := dtPaymentDate.Date;
        Params.ParamByName('PaymentType').AsString := dbcbPaymentType.KeyValue;
        Params.ParamByName('PaymentRef').AsString := txtPaymentReference.Text;
        Params.ParamByName('PaidAmount').AsFloat := StrToFloat(txtPaidAmount.Text);
        Params.ParamByName('Billable').AsString := BoolToStr(chkBillable.Checked, True);
        Params.ParamByName('Remark').AsString := mmRemark.Lines.Text;
        if not Prepared then
          Prepare;
        ExecSQL;
        Transaction.Commit;
      except
        on E: EIBInterBaseError do
        begin
          Transaction.Rollback;
          ShowMessage(Format('IBErrorCode=%d, SQLCode=%d, Msg=%s, StackTrace=%s', [E.IBErrorCode, E.SQLCode, E.Message, E.StackTrace]));
        end;
      end;
    finally
      with DataModule1 do
      begin
        with qryExpenses do
          if not Active then
            Open;
        with dbcbExpenseCategory.ListSource.DataSet do
          if not Active then
            Open;
      end;
    end;
  end;
end;

procedure TfmExpenses.txtAmountExit(Sender: TObject);
begin
  txtAmount.Text := Format('%.2f', [StrToFloat(txtAmount.Text)]);
end;

procedure TfmExpenses.txtAmountKeyPress(Sender: TObject; var Key: Char);
var
  i: integer;
begin
  case Key of
    '0'..'9':;
    '.': begin
           i := Pos('.', txtAmount.Text);
           if i > 0 then
           begin
             Key := #0;
             txtAmount.SelStart := i + 1;
             txtAmount.SelLength := Length(txtAmount.Text) - i;
           end;
         end;
    else
      Key := #0;
  end;
end;

procedure TfmExpenses.txtPaidAmountExit(Sender: TObject);
begin
  txtPaidAmount.Text := Format('%.2f', [StrToFloat(txtPaidAmount.Text)]);
end;

procedure TfmExpenses.txtPaidAmountKeyPress(Sender: TObject; var Key: Char);
var
  i: integer;
begin
  case Key of
    '0'..'9':;
    '.': begin
           i := Pos('.', txtPaidAmount.Text);
           if i > 0 then
           begin
             Key := #0;
             txtPaidAmount.SelStart := i + 1;
             txtPaidAmount.SelLength := Length(txtPaidAmount.Text) - i;
           end;
         end;
    Char(VK_BACK):;
    else
      Key := #0;
  end;
end;

procedure TfmExpenses.UpdateRecord;
begin
  with DataModule1.qrySQL do
  begin
    SQL.Clear;
    Params.Clear;
    try
      try
        SQL.Add('UPDATE EXPENSES SET NAME=:Name, CATEGORY_ID=:CategoryID, ' +
                'PERIOD_YEAR=:Year, PERIOD_MONTH=:Month, AMOUNT=:Amount, ' +
                'REFERENCE=:Reference, PAYMENT_DATE=:PaymentDate, ' +
                'PAYMENT_TYPE=:PaymentType, PAYMENT_REFERENCE=:PaymentRef, ' +
                'DUE_DATE=:DueDate, PAID_AMOUNT=:PaidAmount, BILLABLE=:Billable, ' +
                'REMARK=:Remark WHERE ID=:ID');
        Params.ParamByName('ID').AsInteger := DBGrid1.DataSource.DataSet.FieldByName('ID').AsInteger;
        Params.ParamByName('Name').AsString := cbName.Text;
        Params.ParamByName('CategoryID').AsInteger := dbcbExpenseCategory.KeyValue;
        Params.ParamByName('Year').AsInteger := StrToInt(txtYear.Text);
        Params.ParamByName('Month').AsInteger := StrToInt(txtMonth.Text);
        Params.ParamByName('Amount').AsFloat := StrToFloat(txtAmount.Text);
        Params.ParamByName('Reference').AsString := txtReference.Text;
        Params.ParamByName('DueDate').AsDate := dtDueDate.Date;
        Params.ParamByName('PaymentDate').AsDate := dtPaymentDate.Date;
        Params.ParamByName('PaymentType').AsString := dbcbPaymentType.KeyValue;
        Params.ParamByName('PaymentRef').AsString := txtPaymentReference.Text;
        Params.ParamByName('PaidAmount').AsFloat := StrToFloat(txtPaidAmount.Text);
        Params.ParamByName('Billable').AsString := BoolToStr(chkBillable.Checked, True);
        Params.ParamByName('Remark').AsString := mmRemark.Lines.Text;
        if not Prepared then
          Prepare;
        ExecSQL;
        Transaction.Commit;
      except
        on E: EIBInterBaseError do
        begin
          Transaction.Rollback;
          ShowMessage(Format('IBErrorCode=%d, SQLCode=%d, Msg=%s, StackTrace=%s', [E.IBErrorCode, E.SQLCode, E.Message, E.StackTrace]));
        end;
      end;
    finally
      with DataModule1 do
      begin
        with qryExpenses do
          if not Active then
            Open;
        with dbcbExpenseCategory.ListSource.DataSet do
          if not Active then
            Open;
      end;
    end;
  end;
end;

procedure TfmExpenses.DeleteRecord;
begin
  with DataModule1.qrySQL do
  begin
    SQL.Clear;
    Params.Clear;
    try
      try
        SQL.Add('DELETE FROM EXPENSES WHERE ID=:ID');
        Params.ParamByName('ID').AsInteger := DBGrid1.DataSource.DataSet.FieldByName('ID').AsInteger;
        if not Prepared then
          Prepare;
        ExecSQL;
        Transaction.Commit;
      except
        on E: EIBInterBaseError do
        begin
          Transaction.Rollback;
          ShowMessage(Format('IBErrorCode=%d, SQLCode=%d, Msg=%s, StackTrace=%s', [E.IBErrorCode, E.SQLCode, E.Message, E.StackTrace]));
        end;
      end;
    finally
      with DataModule1 do
      begin
        with qryExpenses do
          if not Active then
            Open;
        with dbcbExpenseCategory.ListSource.DataSet do
          if not Active then
            Open;
      end;
    end;
  end;
end;
end.
